/+
Example sample_01.d - Example of how to use the dbapi library.

Copyright (C) 2007 Myron Alexander (myron.alexander...gmail.com)

Licence: Public domain
+/

import std.boxer;
import std.stdio;
import std.string;

import dbapi   = dbapi.base;
import sqlite3 = dbapi.sqlite3;

import dbapi.helper;

alias dbapi.NamedParameter   np;
alias dbapi.unboxNullable    unboxN;
alias dbapi.unboxNullDefault unboxD;
alias dbapi.isNull           isNull;

void main () {

   auto c = sqlite3.connect ("MyDb.db");
   scope (exit) c.close ();

   auto cu = c.cursor ();
   scope (exit) cu.close ();

   //---------------------------------------------------------------------------

   /* SQL statements must not end with a semi-colon; a multi-statement error
    * will be generated if the semi-colon is used to terminate the statement.
    */
   cu.execute (
      "create table atable (id int, name varchar2 (20), "
      "balance number (10,2))");

   cu.execute ("insert into atable values (1,'First Person',0)");

   cu.execute ("insert into atable values (?,?,?)", 2, "Second Person", 1);

   cu.execute ("insert into atable values (?,?,?)", 3, "Third Person", 123.45);

   cu.execute (
      "insert into atable values (:id, :name, :balance)",
      np("id",4), np("name","Fourth Person"), np("balance",999.99));

   //---------------------------------------------------------------------------

   cu.execute ("select * from atable");

   writefln ("\n=== Table Description ======================================");

   outputDescription (cu);

   writefln ("\n=== select * from atable (no nulls) ========================");

   /* If no nulls are possible, then you can use unbox directly.
    */
   Box[][] values = cu.fetchall ();
   printValues (values);

   //---------------------------------------------------------------------------

   writefln ("\n=== select * from atable where name = :name ================");

   cu.execute (
      "select * from atable where name = :name",
      np("name", "Third Person"));
   values = cu.fetchall ();
   printValues (values);

   //---------------------------------------------------------------------------

   cu.execute ("insert into atable values (?,?,?)", null, null, null);

   cu.execute ("select * from atable");

   writefln ("\n=== select * from atable (nulls) ===========================");

   /* If nulls are expected, then you can use unboxNullable or unboxNullDefault.
    */
   values = cu.fetchall ();
   foreach (Box[] val; values) {
      writefln (
         "%d, %s, %#.2f",
         unboxD!(int)(val[0], -1), unboxD!(char[])(val[1], "<NULL>"),
         unboxD!(double)(val[2], -1.0));
   }

   //---------------------------------------------------------------------------

   cu.execute ("select * from atable");

   writefln ("\n=== select * from atable (alt-nulls) =======================");

   /* Alternatively, you can use the helper functions.
    */
   values = cu.fetchall ();
   foreach (Box[] val; values) {
      writefln (
         "%d, %s, %#.2f",
         val.xint (0), val.xstr (1, "<NULL>"), val.xdbl (2, 0.00));
   }
   
   writefln ("------------------------------------------------------------");
   
   foreach (Box[] val; values) {
      writefln (
         "%d, %s, %#.2f",
         xint (val[0]), xstr (val[1], "<NULL>"), xdbl (val[2], 0.00));
   }

   //---------------------------------------------------------------------------
   
   writefln ("\n=== Insert multiple ========================================");
   
   int[] affectedValues =  cu.executemany (
                              "insert into atable values (?,?,?)"
                              , boxArray (10, "Multi 1", 1.0)
                              , boxArray (11, "Multi 2", 2.0)
                              , boxArray (12, "Multi 3", 3.0)
                              , boxArray (13, "Multi 4", 4.0)
                           );

   writefln ("------------------------------------------------------------");

   writef ("Affected rows: [");
   foreach (i; affectedValues) {
      writef  (" %d ", i);
   }
   writefln ("]");

   writefln ("------------------------------------------------------------");

   cu.execute ("select * from atable");

   /* Alternatively, you can use the helper functions.
    */
   values = cu.fetchall ();
   foreach (Box[] val; values) {
      writefln (
         "%d, %s, %#.2f",
         val.xint (0), val.xstr (1, "<NULL>"), val.xdbl (2, 0.00));
   }
}


void outputDescription (sqlite3.Cursor cu) {
   dbapi.ColumnAttributes[] caa = cu.description;
   foreach (dbapi.ColumnAttributes a ; caa) {
      writefln ("[%s, %s, %d, %d]", a.name, a.typeCode, a.precision, a.scale);
   }
}

void printValues (Box[][] values) {
   if (values is null) {
      writefln ("Nothing returned");
   } else {
      foreach (Box[] val; values) {
         writefln (
            "%d, %s, %#.2f",
            unbox!(int)(val[0]), unbox!(char[])(val[1]),
            unbox!(double)(val[2]));
      }
   }
}
